﻿using System.Configuration;                                                                                 //包含访问配置文件所需的配置管理器；需事先在本项目的“引用”中添加对System.Configuration的引用；
using System.Data;                                                                                          //包含Ado.Net的各类数据对象；
using System.Data.SqlClient;
using Dapper;
namespace EMR
{
    public static class SqlHelper
    {

        //连接字符串
        private static readonly string conStr = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

        public  static string ConnStrings
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
            }
        }


        //执行增删改
        //ExecuteNonQuery();
        public static int ExecuteNonQuery(string sql, bool isStoreProcedure ,params SqlParameter[] sqlparameters)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql,con))
                {
                    if (sqlparameters != null)
                    {
                        cmd.Parameters.AddRange(sqlparameters);
                    }
                    if (isStoreProcedure)
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行查询，返回单行ExecuteScalar();
        /// </summary>
        /// <param name="sql">执行语句</param>
        /// <param name="sqlparameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] sqlparameters)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (sqlparameters != null)
                    {
                        cmd.Parameters.AddRange(sqlparameters);
                    }
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        //执行查询，返回多行
        //ExecuteQuery();
        public static object ExecuteQuery(string sql, params SqlParameter[] sqlParameters)
        {
            SqlConnection con = new SqlConnection(conStr); //不能用using ,当使用Reader时 ，con连接不能关闭。
                using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if(sqlParameters!=null)
                {
                    cmd.Parameters.AddRange(sqlParameters);
                }
                try    //防止执行异常时，con无法关闭。
                {
                    con.Open();
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//使用重载
                }
                catch
                {
                    con.Close();
                    con.Dispose();
                    throw;  //不抛出 会显示 并非所有的代码路径都返回值
                }
            }
        }



    }
}

